package com.bw.mapper;

import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import com.bw.bean.Vo3;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {

    @Select("select s.name'name',sum(oi.original_amount)'value',count(oi.id)'num1',sum(distinct oi.customer_id) 'num2',\n" +
            "       sum(oi.actual_amount)'num3',sum(p.amount)'num4' from order_info oi\n" +
            "left join payment p on oi.id = p.order_info_id\n" +
            "left join shop s on oi.shop_id = s.id\n" +
            "where p.status = 5\n" +
            "group by s.name;")
    List<Vo1> getList1();

    @Select("select curdate()'name',1 'value',sum(case when s.type= 1 then p.reduce_amount end)'num1',\n" +
            "       sum(case when s.type= 2 then p.reduce_amount end)'num2',sum(p.reduce_amount)'num3' from shop s\n" +
            "left join order_info oi on s.id = oi.shop_id\n" +
            "left join promotion p on oi.promotion_id = p.id\n" +
            "where timestampdiff(year,oi.create_time,now())>=1;")
    List<Vo2> getList2();

    @Select("select p.name,count(distinct oi.id)/count(oi.rating)'value' from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_info_id\n" +
            "left join product_sku ps on od.product_sku_id = ps.id\n" +
            "left join product_spu p on ps.product_spu_id = p.id\n" +
            "where oi.rating = 5\n" +
            "group by p.name;")
    List<Vo3> getList3();

    @Select("select p.name,avg(oi.rating)'value' from order_info oi\n" +
            "left join order_detail od on oi.id = od.order_info_id\n" +
            "left join product_sku ps on od.product_sku_id = ps.id\n" +
            "left join product_spu p on ps.product_spu_id = p.id\n" +
            "group by p.name;")
    List<Vo3> getList4();

    @Select("select create_time name,sum(original_amount)'value' from order_info group by create_time;")
    List<Vo3> getList5();
}
